package com.qiujie.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.qiujie.entity.Role;
import com.qiujie.entity.Staff;
import com.qiujie.entity.StaffLeave;
import com.qiujie.exception.ServiceException;
import com.qiujie.vo.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Set;

public interface StaffMapper extends BaseMapper<Staff> {

    @Select("SELECT ss.id staff_id, ss.dept_id, ss.code, ss.number, ss.name, ss.phone, ss.address, sd.name dept_name " + // 新增number字段
            "FROM sys_staff ss INNER JOIN sys_dept sd ON ss.dept_id = sd.id " +
            "WHERE ss.is_deleted = 0 AND ss.name LIKE CONCAT('%',#{name},'%')")
    IPage<StaffAttendanceVO> listStaffAttendanceVO(IPage<StaffAttendanceVO> config, @Param("name") String name);

    @Select("SELECT ss.id staff_id, ss.dept_id, ss.code, ss.number, ss.name, ss.phone, ss.address, sd.name dept_name " + // 新增number字段
            "FROM sys_staff ss INNER JOIN sys_dept sd ON ss.dept_id = sd.id " +
            "WHERE ss.is_deleted = 0 AND ss.dept_id = #{deptId} AND ss.name LIKE CONCAT('%',#{name},'%')")
    IPage<StaffAttendanceVO> listStaffDeptAttendanceVO(IPage<StaffAttendanceVO> config, @Param("name") String name, @Param("deptId") Integer deptId);

    @Select("SELECT ss.id staff_id, ss.dept_id, ss.code, ss.name, ss.phone, ss.address, sd.name dept_name " +
            "FROM sys_staff ss INNER JOIN sys_dept sd ON ss.dept_id = sd.id WHERE ss.is_deleted = 0")
    List<AttendanceMonthVO> queryAttendanceMonthVO();

    @Select("SELECT ss.id, ss.code, ss.number, ss.name, ss.gender, ss.pwd password, ss.avatar, ss.birthday, " +
            "ss.phone, ss.address, ss.remark, ss.status, ss.dept_id, sd.name dept_name " +
            "FROM sys_staff ss LEFT JOIN sys_dept sd ON ss.dept_id = sd.id " +
            "WHERE ss.is_deleted = 0 AND ss.code = #{code}")
    StaffDeptVO queryByCode(@Param("code") String code);

    @Select("SELECT ss.id, ss.code, ss.number, ss.name, ss.gender, ss.avatar, ss.birthday, ss.phone, " +
            "ss.address, ss.remark, ss.status, ss.dept_id, sd.name dept_name " +
            "FROM sys_staff ss LEFT JOIN sys_dept sd ON ss.dept_id = sd.id " +
            "WHERE ss.is_deleted = 0 AND ss.id = #{id}")
    StaffDeptVO queryInfo(@Param("id") Integer id);

    @Select("SELECT ss.id, ss.code, ss.number, ss.name, ss.gender, ss.pwd password, ss.avatar, ss.birthday, " +
            "ss.phone, ss.address, ss.remark, ss.status, ss.dept_id, sd.name dept_name " +
            "FROM sys_staff ss LEFT JOIN sys_dept sd ON ss.dept_id = sd.id WHERE ss.is_deleted = 0")
    List<StaffDeptVO> queryStaffDeptVO();

    @Select("SELECT ss.id staff_id, ss.dept_id, ss.code, ss.name, ss.phone, ss.address, sd.name dept_name " +
            "FROM sys_staff ss INNER JOIN sys_dept sd ON ss.dept_id = sd.id " +
            "WHERE ss.is_deleted = 0 AND ss.name LIKE CONCAT('%',#{name},'%')")
    IPage<StaffOvertimeVO> listStaffOvertimeVO(IPage<StaffOvertimeVO> config, @Param("name") String name);

    @Select("SELECT ss.id staff_id, ss.dept_id, ss.code, ss.name, ss.phone, ss.address, sd.name dept_name " +
            "FROM sys_staff ss INNER JOIN sys_dept sd ON ss.dept_id = sd.id " +
            "WHERE ss.is_deleted = 0 AND ss.dept_id = #{deptId} AND ss.name LIKE CONCAT('%',#{name},'%')")
    IPage<StaffOvertimeVO> listStaffDeptOvertimeVO(IPage<StaffOvertimeVO> config, @Param("name") String name, @Param("deptId") Integer deptId);

    @Select("SELECT ss.id staff_id, ss.dept_id, ss.code, ss.name, ss.phone, ss.address, sd.name dept_name " +
            "FROM sys_staff ss INNER JOIN sys_dept sd ON ss.dept_id = sd.id WHERE ss.is_deleted = 0")
    List<OvertimeMonthVO> queryOvertimeMonthVO();



    /**
     * 根据角色编码查询员工
     */
    @Select("SELECT s.* FROM sys_staff s " +
            "INNER JOIN per_role_level prl ON prl.role_code = #{roleCode} " +
            "WHERE s.role_level = prl.level " +
            "AND s.is_deleted = 0")
    List<Staff> findByRole(@Param("roleCode") String roleCode);





    @Select("SELECT s.* FROM sys_staff s " +
            "INNER JOIN per_staff_role psr ON s.id = psr.staff_id " +
            "INNER JOIN per_role pr ON psr.role_id = pr.id " +
            "WHERE s.dept_id = #{deptId} AND pr.code = #{roleCode}")
    List<Staff> findByRoleAndDept(@Param("roleCode") String roleCode, @Param("deptId") Integer deptId);


    @Select("<script>" +
            "SELECT * FROM sys_staff WHERE " +
            "<foreach item='role' collection='roles' separator=' OR '>" +
            "JSON_CONTAINS(roles, #{role})" +
            "</foreach>" +
            "</script>")
    List<Staff> findByRoles(@Param("roles") List<String> roles);


    @Select("SELECT pr.code FROM per_role pr " +
            "INNER JOIN per_staff_role psr ON pr.id = psr.role_id " +
            "WHERE psr.staff_id = #{staffId}")
    Set<String> getRolesByStaffId(@Param("staffId") Integer staffId);



    // 个人请假记录查询
    @Select("SELECT l.* FROM att_staff_leave l " +
            "INNER JOIN sys_staff s ON l.staff_id = s.id " +
            "WHERE s.code = #{code}")
    IPage<StaffLeave> listPersonalLeaves(Page<StaffLeave> page, @Param("code") String code);

    // 待审批记录查询
    @Select("SELECT l.* FROM att_staff_leave l " +
            "INNER JOIN att_approve_record r ON l.id = r.leave_id " +
            "WHERE r.approver_role = #{role} " +
            "AND r.status = 0 " + // 0表示待处理
            "AND l.status = 0")   // 请假单状态为待审批
    IPage<StaffLeave> listPendingApprovals(Page<StaffLeave> page, @Param("role") String role);



}

